﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

//TODO: Passar a recuperar os parâmetros das procedures, functions e packages
namespace PSE.Tools.SchemaComparer.Extractor
{
    [Serializable]
    public class ScriptObject : DBObject
    {
        //TODO:Trocar ObjectType para Enum
        public string ObjectType
        {
            get;
            set;
        }

        public string BodyLength
        {
            get;
            set;
        }

        public string Body
        {
            get;
            set;
        }

        public static DBObjectList<ScriptObject> LoadAllFunctions(OracleConnection connection)
        {
            return LoadAll(Constants.FUNCTION, connection);
        }

        public static DBObjectList<ScriptObject> LoadAllProcedures(OracleConnection connection)
        {
            return LoadAll(Constants.PROCEDURE, connection);
        }

        private static DBObjectList<ScriptObject> LoadAll(string objType, OracleConnection connection)
        {
            DBObjectList<ScriptObject> ret;
            StringBuilder sqlCommand = new StringBuilder();

            //sqlCommand.Append(" SELECT DISTINCT ALL_OBJECTS.OWNER, USER_PROCEDURES.OBJECT_NAME");
            //sqlCommand.Append(" FROM  ALL_OBJECTS, USER_PROCEDURES");
            //sqlCommand.Append(" WHERE USER_PROCEDURES.OBJECT_NAME = ALL_OBJECTS.OBJECT_NAME ");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_ID = ALL_OBJECTS.OBJECT_ID");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_TYPE = ALL_OBJECTS.OBJECT_TYPE");
            //sqlCommand.Append(" AND USER_PROCEDURES.OBJECT_TYPE = :OBJ_TYPE");
            //sqlCommand.Append(" ORDER BY USER_PROCEDURES.OBJECT_NAME");

            //recuperar o usuario utilizado para conectar para limitar o retorno da query
            string owner = (new OracleConnectionStringBuilder(connection.ConnectionString)).UserID;

            sqlCommand.Append(" SELECT DISTINCT ALL_OBJECTS.OWNER, USER_PROCEDURES.OBJECT_NAME");
            sqlCommand.Append(" FROM  ALL_OBJECTS, USER_PROCEDURES");
            sqlCommand.Append(" WHERE USER_PROCEDURES.OBJECT_NAME = ALL_OBJECTS.OBJECT_NAME ");
            sqlCommand.Append(" AND UPPER(ALL_OBJECTS.OBJECT_TYPE) = UPPER(:OBJ_TYPE)");
            sqlCommand.Append(" AND UPPER(ALL_OBJECTS.OWNER) = UPPER(:OBJ_OWNER)");
            sqlCommand.Append(" ORDER BY USER_PROCEDURES.OBJECT_NAME");

            ScriptObject scriptObj;
            OracleCommand commnad = new OracleCommand(sqlCommand.ToString(), connection);
            commnad.Parameters.Add(new OracleParameter("OBJ_TYPE", objType));
            commnad.Parameters.Add(new OracleParameter("OBJ_OWNER", owner));
            OracleDataReader reader = commnad.ExecuteReader();

            ret = new DBObjectList<ScriptObject>();
            while (reader.Read())
            {
                scriptObj = new ScriptObject();
                scriptObj.Name = reader.GetValue<string>("OBJECT_NAME");
                scriptObj.ObjectType = objType;

                owner = reader.GetValue<string>("OWNER");
                //trocar o final de linha para o padrao DOS
                scriptObj.Body = UnixToDosConverter.UnixToDos(LoadScriptObjectBody(scriptObj, connection));
                //remover o nome do owner do str
                scriptObj.Body = scriptObj.Body.Replace(string.Format("\"{0}\".", owner), "");
                //remover nova linha ou espacos do inicio e do final da string
                scriptObj.Body = scriptObj.Body.Trim('\r', '\n', ' ');
                //recuperar o numero de linhas
                scriptObj.BodyLength = scriptObj.Body.Length.ToString();

                ret.Add(scriptObj);
            }

            return ret;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static string LoadScriptObjectBody(ScriptObject obj, OracleConnection connection)
        {
            string ret = string.Empty;
            StringBuilder sqlCommand = new StringBuilder();

            //TODO: Realizar testes com SELECT FROM ALL_SOURCE para verificar se performance melhora
            sqlCommand.Append(" SELECT DBMS_METADATA.GET_DDL (:OBJ_TYPE,:OBJ_NAME,'') RESULT FROM DUAL");

            OracleCommand commnad = new OracleCommand(sqlCommand.ToString(), connection);
            commnad.Parameters.Add(new OracleParameter("OBJ_NAME", obj.Name));
            commnad.Parameters.Add(new OracleParameter("OBJ_TYPE", obj.ObjectType));

            OracleDataReader reader = commnad.ExecuteReader();

            if (reader.Read())
                ret = reader.GetValue<string>("RESULT");

            return ret;

        }
    }
}
